﻿Imports DTO
Imports System.Data.OleDb

Public Class BangDiemDAO
    Public Sub ThemBangDiem(ByVal BangDiem As BangDiemDTO)

        Dim sql As String = "INSERT INTO CHITIETDIEM(MAHOCSINH,DIEM15P,DIEM1T,DIEMHK,MADIEMMONHOC) VALUES ("
        sql += BangDiem.MaHocSinh.ToString
        sql += " ," + BangDiem.Diem15P.ToString
        sql += " ," + BangDiem.Diem1T.ToString
        sql += " ," + BangDiem.DiemHK.ToString
        sql += " ," + BangDiem.MaChiTietDiem.ToString
        sql += ")"
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim cm As New OleDbCommand(sql, cn)
        cm.ExecuteNonQuery()

    End Sub
    Public Sub CapNhatBangDiem(ByVal dsBangDiem As List(Of BangDiemDTO))
        For i As Integer = 0 To dsBangDiem.Count - 1
            Dim sql As String = "UPDATE CHITIETDIEM SET"
            sql += " DIEM15P = " + dsBangDiem(i).Diem15P.ToString
            sql += " ,DIEM1T = " + dsBangDiem(i).Diem1T.ToString
            sql += " ,DIEMHK = " + dsBangDiem(i).DiemHK.ToString
            sql += " WHERE MACHITIETDIEM = " + dsBangDiem(i).MaChiTietDiem.ToString
            Dim cn As OleDbConnection = DataProvider.ConnectDB()
            Dim cm As New OleDbCommand(sql, cn)
            cm.ExecuteNonQuery()
        Next

    End Sub
    Public Sub CapNhatBangDiem(ByVal BangDiem As BangDiemDTO)
        Dim sql As String = "UPDATE CHITIETDIEM SET"
        sql += " DIEM15P = " + BangDiem.Diem15P.ToString
        sql += " ,DIEM1T = " + BangDiem.Diem1T.ToString
        sql += " ,DIEMHK = " + BangDiem.DiemHK.ToString
        sql += " WHERE MACHITIETDIEM = " + BangDiem.MaChiTietDiem.ToString
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim cm As New OleDbCommand(sql, cn)
        cm.ExecuteNonQuery()
        cn.Close()
    End Sub
    Public Function LayBangDiem(ByVal MaHocSinh As Integer) As DataTable
        Dim sql = "SELECT CHITIETDIEM.MACHITIETDIEM, MONHOC.MAMON, MONHOC.TENMON, CHITIETDIEM.DIEM15P,CHITIETDIEM.DIEM1T,CHITIETDIEM.DIEMHK,HOCKY.HOCKY,HOCKY.MAHOCKY,CHITIETDIEM.MADIEMMONHOC,HOCSINH.MALOP,HOCSINH.MAHOCSINH FROM HOCSINH,MONHOC,DIEMMONHOC,CHITIETDIEM,HOCKY"
        sql += " WHERE HOCSINH.MAHOCSINH = " + MaHocSinh.ToString
        sql += " AND HOCSINH.MALOP = DIEMMONHOC.MALOP AND DIEMMONHOC.MAMON = MONHOC.MAMON AND DIEMMONHOC.MAHOCKY = HOCKY.MAHOCKY AND DIEMMONHOC.MADIEMMONHOC = CHITIETDIEM.MADIEMMONHOC AND CHITIETDIEM.MAHOCSINH = HOCSINH.MAHOCSINH"
        Dim cn As OleDbConnection = DataProvider.ConnectDB
        Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sql, cn)
        Dim databtable As New DataTable
        adapter.Fill(databtable)
        cn.Close()
        Return databtable
    End Function
    Public Sub XoaDiemCuaMotMon(ByVal BangDiem As BangDiemDTO)
        ' xóa trong bảng chitietdiem
        Dim sql = "DELETE FROM CHITIETDIEM WHERE MACHITIETDIEM = "
        sql += BangDiem.MaChiTietDiem.ToString
        Dim cn As OleDbConnection = DataProvider.ConnectDB
        Dim cm As New OleDbCommand(sql, cn)
        cm.ExecuteNonQuery()
        'xoa trong bảng diemmonhoc
        'Dim sql2 = "DELETE FROM DIEMMONHOC WHERE MADIEMMONHOC = "
        'sql2 += BangDiem.MaDiemMonHoc.ToString
        'Dim cm2 As New OleDbCommand(sql2, cn)
        'cm2.ExecuteNonQuery()
        cn.Close()
    End Sub
    Public Function KiemTra(ByVal MaHocSinh As Integer, ByVal MaDiemMonHoc As Integer) As Integer
        Dim sql As String = "SELECT COUNT(*) FROM CHITIETDIEM WHERE"
        sql += " MADIEMMONHOC = " + MaDiemMonHoc.ToString
        sql += " AND MADIEMMONHOC = " + MaDiemMonHoc.ToString
        Dim cn As OleDbConnection = DataProvider.ConnectDB
        Dim cm As New OleDbCommand(sql, cn)
        Return cm.ExecuteScalar()
    End Function
End Class
